﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Configuration;
using System.Reflection.Emit;
using System.Reflection;
using System.Data;

namespace DbTools
{
    public class DbHelper
    {
        private static DbHelper helper;

        public static DbHelper getHelper()
        {
            return DbHelper.getHelper(null);
        }

        public static DbHelper getHelper(string connString)
        {
            if (DbHelper.helper == null)
            {
                DbHelper.helper = new DbHelper(connString);
            }
            return DbHelper.helper;
        }

        private string connectString;
        
        public string ConnectString
        {
            get { return connectString; }
            set { connectString = value; }
        }
        private SqlConnection connection;

        public SqlConnection Connection
        {
            get { return connection; }
            set { connection = value; }
        }

        /// <summary>
        /// 类构造器
        /// </summary>
        private DbHelper()
        {
            Initialize(null);
        }

        /// <summary>
        /// 类构造器
        /// </summary>
        /// <param name="connString">链接字符串</param>
        private DbHelper(string connString)
        {
            Initialize(connString);
        }

        /// <summary>
        /// 初使化方法
        /// </summary>
        private void Initialize()
        {
            Initialize(null);
        }

        /// <summary>
        /// 初使化方法
        /// </summary>
        /// <param name="connString">链接字符串</param>
        private void Initialize(String connString)
        {
            if (!String.IsNullOrEmpty(connString))
            {
                this.connectString = connString;
            }
            else
            {
                this.connectString = "";
            }
            using (this.connection = new SqlConnection(connectString))
            {
                this.connection.Open();
            }
        }

        /// <summary>
        /// 获得一个已设置好参数的SqlCommand
        /// </summary>
        /// <param name="sql">SQL字符串</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>SqlCommand</returns>
        public SqlCommand GetPreparedCommand(string sql, params object[] parm)
        {
            SqlCommand scb = new SqlCommand();
            if (this.connection != null && this.connection.State != System.Data.ConnectionState.Open)
            {
                this.connection.ConnectionString = this.connectString;
                this.connection.Open();
            }
            scb.Connection = this.connection;
            scb.CommandText = sql;
            scb.CommandType = System.Data.CommandType.Text;
            int count = 0;
            foreach (string name in GetParmeterNames(sql))
            {
                scb.Parameters.Add(new SqlParameter(name, parm[count++]));
            }
            return scb;
        }

        /// <summary>
        /// 获得一个已设置好参数的SqlCommand
        /// </summary>
        /// <param name="sql">SQL字符串</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>SqlCommand</returns>
        public DataSet ExcuteProcudure(string procudre, Hashtable param)
        {
            SqlCommand scb = new SqlCommand();
            if (this.connection != null && this.connection.State != System.Data.ConnectionState.Open)
            {
                this.connection.ConnectionString = this.connectString;
                this.connection.Open();
            }
            scb.Connection = this.connection;
            scb.CommandText = procudre;
            scb.CommandType = System.Data.CommandType.StoredProcedure;
            foreach (string key in param.Keys)
            {
                scb.Parameters.Add(new SqlParameter(key, param[key]));
            }
            DataSet ret = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = scb;
            sda.Fill(ret);
            return ret;
        }

        /// <summary>
        /// 执行查询，返回SqlDataReader
        /// </summary>
        /// <param name="sql">SQL字符串</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader ExcuteQuery(string sql, params object[] parm)
        {
            return GetPreparedCommand(sql, parm).ExecuteReader();
        }

        /// <summary>
        /// 执行查询，返回DataTable
        /// </summary>
        /// <param name="sql">SQL字符串</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>DataTable</returns>
        public DataTable ExcuteDataTableQuery(string sql, params object[] parm)
        {
            SqlCommand scb = GetPreparedCommand(sql, parm);
            SqlDataAdapter adapter = new SqlDataAdapter(scb);
            DataTable ret = new DataTable();
            adapter.Fill(ret);
            return ret;
        }

        /// <summary>
        /// 执行查询，返回DataSet
        /// </summary>
        /// <param name="sql">SQL字符串</param>
        /// <param name="paras">SQL参数</param>
        /// <returns>DataSet</returns>
        public DataSet ExcuteDataSetQuery(string sql, params object[] parm)
        {
            SqlCommand scb = GetPreparedCommand(sql, parm);
            SqlDataAdapter adapter = new SqlDataAdapter(scb);
            DataSet ret = new DataSet();
            adapter.Fill(ret);
            return ret;
        }

        /// <summary>
        /// 找到并返回SQL中所有参数名称
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>List</returns>
        private List<string> GetParmeterNames(string sql)
        {
            List<string> names = new List<string>();
            Regex reg = new Regex(@"\@[a-zA-Z0-9]+");
            foreach(Match match in reg.Matches(sql))
            {
                if (!names.Contains(match.Value))
                {
                    names.Add(match.Value);
                }
                
            }
            return names;
        }

        /// <summary>
        /// 用List生成DataTable
        /// </summary>
        /// <param name="list">List</param>
        /// <returns>DataTable</returns>
        public DataTable setListToDataTable<T>(List<T> list)
        {
            DataTable ret = new DataTable();
            PropertyInfo[] prop = typeof(T).GetProperties(BindingFlags.Public |
                                        BindingFlags.Static |
                                        BindingFlags.NonPublic |
                                        BindingFlags.Instance |
                                        BindingFlags.DeclaredOnly);

            foreach (PropertyInfo info in prop)
            {
                DataColumn dc = new DataColumn(info.Name, info.PropertyType);
                ret.Columns.Add(dc);
            }
            foreach (T val in list)
            {
                DataRow dr = ret.NewRow();
                foreach (PropertyInfo info in prop)
                {
                    dr[info.Name] = info.GetValue(val, null);
                }
                ret.Rows.Add(dr);
            }
            return ret;

        }

        /// <summary>
        /// 用DataTable生成List
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <returns>List</returns>
        public List<T> setDataTableToList<T>(DataTable table)
        {
            List<T> ret = new List<T>();
            PropertyInfo[] prop = typeof(T).GetProperties(BindingFlags.Public |
                                        BindingFlags.Static |
                                        BindingFlags.NonPublic |
                                        BindingFlags.Instance |
                                        BindingFlags.DeclaredOnly);

            foreach (DataRow dr in table.Rows)
            {
                T val = (T)Activator.CreateInstance(typeof(T));
                foreach (PropertyInfo info in prop)
                {
                    info.SetValue(val, dr[info.Name], null);
                }
                ret.Add(val);
            }
            return ret;
        }



        /// <summary>
        /// 基础类型初始值比较
        /// </summary>
        /// <param name="val">泛型值</param>
        /// <returns>true:是，false:否</returns>
        private bool IsDefaultValue<T>(T val)
        {
            Object obj = val;
            if(typeof(T)==typeof(int))
            {
                    int intValue = default(int);
                    return intValue == (int)obj;
            }
            if(typeof(T)==typeof(double))
            {
                    double dbValue = default(double);
                    return dbValue == (double)obj;
            }
            if(typeof(T)==typeof(string))
            {
                    string stringVal = default(string);
                    return stringVal == (string)obj;
            }
            if(typeof(T)==typeof(bool))
            {
                    bool boolVal = default(bool);
                    return boolVal == (bool)obj;
            }
            if(typeof(T)==typeof(long))
            {
                    long longVal = default(long);
                    return longVal == (long)obj;
            }
            if(typeof(T)==typeof(decimal))
            {
                    decimal decVal = default(decimal);
                    return decVal == (decimal)obj;
            }
            return false;
        }

        /// <summary>
        /// 执行非查询语句，返回影响行数
        /// </summary>
        /// <param name="sql">SQL文字列</param>
        /// <param name="param">参数列表</param>
        /// <returns>影响行数</returns>
        public int ExcuteUpdate(string sql,params object[] param)
        {
            return this.GetPreparedCommand(sql, param).ExecuteNonQuery();
        }
    }
}
